package mya.mes.mdc.module.service.impl;

import mya.mes.mdc.model.excel.ExcelFileData;
import mya.mes.mdc.model.system.TagType;
import mya.mes.mdc.module.service.ExcelImportService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.StringUtil;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelImportServiceImpl implements ExcelImportService {

    boolean processFormula = false;
    DataFormatter formatter = new DataFormatter();
    FormulaEvaluator evaluator = null;

    @Override
    public List<TagType> tagDefExcel(ExcelFileData excel) {
        List<TagType> list = new ArrayList<TagType>();
        try {
            System.out.println("开始解析Excel文件：" + excel.excelFile);
            Workbook wb = null;
            try {
                wb = WorkbookFactory.create(new File(excel.excelFile));

                evaluator = wb.getCreationHelper().createFormulaEvaluator();
                Sheet sheet0 = wb.getSheetAt(0);
                for (Row row : sheet0) {
                    if (row.getRowNum() < 1) {
                        //忽略表格的标题行
                        continue;
                    }
                    TagType tagDef = new TagType();

                    tagDef.lineName = getString(row, 0);
                    tagDef.tagName = getString(row, 1);
                    tagDef.description = getString(row, 2);
                    tagDef.rate = getInteger(row, 3);
                    if (StringUtils.isNotBlank(tagDef.lineName) && StringUtils.isNotBlank(tagDef.lineName) && StringUtils.isNotBlank(tagDef.description)) {
                        list.add(tagDef);
                    }

                }
                wb.close();
                wb = null;
            } finally {
                if (wb != null) {
                    wb.close();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    private String getString(Row row, int cellIndex) {

        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return null;
        }

        CellType cellType = cell.getCellType();

        if (CellType.BLANK.equals(cellType)) {
            return null;
        }


        if (CellType.FORMULA.equals(cellType)) {

            if (!processFormula) {
                return null;
            }
            CellValue cellValue = evaluator.evaluate(cell);

            CellType cellType1 = cellValue.getCellType();


            if (CellType.STRING.equals(cellType1)) {
                return cellValue.getStringValue();
            } else if (CellType.ERROR.equals(cellType1)) {
                return null;
            } else if (CellType.BLANK.equals(cellType1)) {
                return null;
            } else {
                return cellValue.formatAsString();
            }

        }
        String text = formatter.formatCellValue(cell);
        if (text != null) {
            text = text.trim();
        }
        return text;
    }

    private Integer getInteger(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return null;
        }

        CellType cellType = cell.getCellType();

        if (CellType.BLANK.equals(cellType) || CellType.ERROR.equals(cellType)) {
            return null;
        }


        if (CellType.FORMULA.equals(cellType)) {
            if (!processFormula) {
                return null;
            }
            CellValue cellValue = evaluator.evaluate(cell);

            CellType cellType1 = cellValue.getCellType();

            if (CellType.BLANK.equals(cellType1) || CellType.ERROR.equals(cellType1)) {
                return null;
            }

            if (CellType.NUMERIC.equals(cellType1)) {
                return new Double(cellValue.getNumberValue()).intValue();
            }


            if (CellType.STRING.equals(cellType1)) {
                String text = cellValue.getStringValue();
                return getAsInt(text);
            }

            return null;

//      if (CellType.NUMERIC.equals(cellType1)) {
//        return cellValue.getStringValue();
//      } else if (CellType.ERROR.equals(cellType1)) {
//        return null;
//      } else if (CellType.BLANK.equals(cellType1)) {
//        return null;
//      } else {
//        return cellValue.formatAsString();
//      }

        }


        if (CellType.NUMERIC.equals(cellType)) {

            return new Double(cell.getNumericCellValue()).intValue();
        } else if (CellType.STRING.equals(cellType)) {

            String text = formatter.formatCellValue(cell);
            return getAsInt(text);

        } else if (CellType.FORMULA.equals(cellType)) {
            cell.getCellFormula();
        }

        return null;
    }

    private Integer getAsInt(String text) {
        if (text != null) {
            text = text.trim();
        }

        if (text == null || text.isEmpty()) {
            return null;
        }

        try {
            return Integer.parseInt(text);
        } catch (Exception e) {
            return null;
        }

    }
}
